package com.unionpay.mpds.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Properties;

import org.apache.tools.ant.Project;
import org.apache.tools.ant.taskdefs.SQLExec;

public class JavaMySQL {

	/**
	 * mysql数据备份 接收脚本名，并返回此路径
	 * 
	 * @param sql为备份的脚本名比如xxx
	 *            .sql
	 * @param sqlpath
	 *            备份文件的路径
	 * @throws IOException
	 */
	public static void backup(String sql, String sqlpath) throws IOException {
		Properties pros = getPprVue("mysql.properties");
		// 这里是读取的属性文件，也可以直接使用
		String username = pros.getProperty("username");
		String password = pros.getProperty("password");
		// 得到MYSQL的用户名密码后调用 mysql 的 cmd:
		String mysqlpaths = pros.getProperty("mysqlpath");
		String databaseName = pros.getProperty("databaseName");
		String address = pros.getProperty("address");
		// String sqlpath = pros.getProperty("sql");
		File backupath = new File(sqlpath);
		if (!backupath.exists()) {
			backupath.mkdir();
		}
		String path = sqlpath + sql;
		StringBuffer sb = new StringBuffer();
		if (mysqlpaths.contains(" ")) {
			sb.append(mysqlpaths.replace(" ", "\" \""));
		} else {
			sb.append(mysqlpaths);
		}

		// sb.append("\"");
		sb.append("mysqldump ");
		sb.append("--opt ");
		sb.append("-h ");
		sb.append(address);
		sb.append(" ");
		sb.append("--user=");
		sb.append(username);
		sb.append(" ");
		sb.append("--password=");
		sb.append(password);
		sb.append(" ");
		sb.append("--lock-all-tables=true ");
		sb.append("--result-file=");
		// sb.append(sqlpath);
		// sb.append(sql);
		if (path.contains(" ")) {
			sb.append(path.replace(" ", "\" \""));
		} else {
			sb.append(path);
		}
		sb.append(" ");
		sb.append("--default-character-set=utf8 ");
		sb.append(databaseName);
		// Runtime cmd = Runtime.getRuntime();
		String[] cmd = { "cmd", "/c", sb.toString() };
		// try {
		Runtime.getRuntime().exec(cmd);
		// Process p = cmd.exec(sb.toString());
		// } catch (IOException e) {
		// e.printStackTrace();
		// }
	}

	// 读取属性值
	public static Properties getPprVue(String properName) {
		InputStream inputStream = JavaMySQL.class.getClassLoader()
				.getResourceAsStream(properName);
		Properties p = new Properties();
		try {
			p.load(inputStream);
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return p;
	}

	/**
	 * 恢复数据库
	 * 
	 * @param filename
	 *            文件名
	 * @param sqlpath
	 *            sql脚本路径
	 * @throws IOException
	 */
	public static void load(String filepath) throws IOException {
		Properties pros = getPprVue("mysql.properties");
		// 这里是读取的属性文件，也可以直接使用
		String root = pros.getProperty("local.username");
		String pass = pros.getProperty("local.password");
		String databaseName = pros.getProperty("databaseName");
		// 得到MYSQL的用户名密码后调用 mysql 的 cmd:
		String mysqlpaths = "";
		if (pros.getProperty("mysqlpath").contains(" ")) {
			mysqlpaths = pros.getProperty("mysqlpath").replace(" ", "\" \"");
		} else {
			mysqlpaths = pros.getProperty("mysqlpath");
		}
		// String sqlpath = pros.getProperty("sql");
		// String filepath = sqlpath + filename; // 备份的路径地址
		// 新建数据库finacing
		String stmt1 = mysqlpaths + "mysqladmin -u " + root + " -p" + pass
				+ " create " + databaseName;
		// -p后面加的是你的密码
		String stmt2 = "";
		if (filepath.contains(" ")) {
			stmt2 = mysqlpaths + "mysql -u " + root + " -p" + pass + " "
					+ databaseName + " < " + filepath.replace(" ", "\" \"");
		} else {
			stmt2 = mysqlpaths + "mysql -u " + root + " -p" + pass + " "
					+ databaseName + " < " + filepath;
		}
		String[] cmd1 = { "cmd", "/c", stmt1 };
		String[] cmd2 = { "cmd", "/c", stmt2 };
		// try {
		Runtime.getRuntime().exec(cmd1);
		Runtime.getRuntime().exec(cmd2);
		// System.out.println("数据已从 " + filepath + " 导入到数据库中");
		// } catch (IOException e) {
		// e.printStackTrace();
		// }
	}

	// 根据key读取value
	public static String readValue(String filePath, String key) {
		Properties props = new Properties();
		try {
			InputStream in = new BufferedInputStream(new FileInputStream(
					filePath));
			props.load(in);
			String value = props.getProperty(key);
			// System.out.println(key + value);
			return value;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	// 写入properties信息
	public static void writeProperties(String filePath, String parameterName,
			String parameterValue) {
		Properties prop = new Properties();
		try {
			InputStream fis = new FileInputStream(filePath);
			// 从输入流中读取属性列表（键和元素对）
			prop.load(fis);
			// 调用 Hashtable 的方法 put。使用 getProperty 方法提供并行性。
			// 强制要求为属性的键和值使用字符串。返回值是 Hashtable 调用 put 的结果。
			OutputStream fos = new FileOutputStream(filePath);
			prop.setProperty(parameterName, parameterValue);
			// 以适合使用 load 方法加载到 Properties 表中的格式，
			// 将此 Properties 表中的属性列表（键和元素对）写入输出流
			prop.store(fos, "Update '" + parameterName + "' value");
		} catch (IOException e) {
			System.err.println("Visit " + filePath + " for updating "
					+ parameterName + " value error");
		}
	}

	public static void main(String[] args) throws IOException {
		// SimpleDateFormat dateFormat = new
		// SimpleDateFormat("yyyy-MM-dd-hh-mm-ss");
		// String date = dateFormat.format(new Date());
		// backup(date+".sql", "E:\\MySQL\\");

		// load("athena.sql");
		// execSqlFile("D:\\test\\ROOT\\WEB-INF\\update\\update.sql");
	}

	public static void execSqlFile(String sqlFile) throws IOException {

		Properties pros = getPprVue("application.properties");
		// 这里是读取的属性文件，也可以直接使用

		String userName=pros.getProperty("dataSource.user");
		String password=pros.getProperty("dataSource.password");
		String url = pros.getProperty("dataSource.url");
		String driver = pros.getProperty("dataSource.driverClass");

		SQLExec sqlExec = new SQLExec();

		sqlExec.setDriver(driver);

		sqlExec.setUrl(url);

		sqlExec.setUserid(userName);

		sqlExec.setPassword(password);

		File file = new File(sqlFile);

		// BufferedReader bf=new BufferedReader(new FileReader(file));
		//	        
		// String tmp="";
		//	        
		// while((tmp=bf.readLine())!=null){
		// System.out.print(tmp);
		// }

		sqlExec.setSrc(file);
		// sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(SQLExec.OnError.class,
		// "abort")));
		sqlExec.setPrint(false); // 设置是否输出

		// 输出到文件 sql.out 中；不设置该属性，默认输出到控制台
		// sqlExec.setOutput(new File("d:/script/sql.out"));

		sqlExec.setProject(new Project()); // 要指定这个属性，不然会出错

		sqlExec.execute();

	}
}